package com.edu.gdkm.dao;

import com.edu.gdkm.db.stor.*;
import com.edu.gdkm.db.table.*;
import com.edu.gdkm.util.dbUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class dypDao {

    dbUtil util = new dbUtil();
    private final Connection conn;
    private final Statement stat;

    public dypDao() throws SQLException, ClassNotFoundException {
        conn = util.getConn();
        stat = conn.createStatement();
    }

    public PreparedStatement creatPs(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }

    /**
     * 原始查询功能
     */
    public ResultSet select(String sql) throws SQLException {
        return stat.executeQuery(sql);
    }

    /**
     * 重写：根据 (列名，表名) 查询
     * */
    public ResultSet select(String c, String t) throws SQLException {
        return this.select("select "+c+" from "+t);
    }

    public ResultSet select(String c, String t, String st, String ed) throws SQLException {
        return this.select("select "+c+" from "+t+" limit "+String.join(",", st, ed));
    }

    public ResultSet selectOrderBy(String c, String t) throws SQLException {
        return this.select("select "+c+" from "+t+" order by "+c);
    }

    public ResultSet selectGroupBy(String c, String t) throws SQLException {
        return this.select("select "+c+" from "+t+" group by "+c);
    }
    /**
     *  重写：根据 (列名，表面，条件) 查询
     * */
    public ResultSet select(String c, String t, String w) throws SQLException {
        return this.select("select "+c+" from "+t+" where "+w);
    }

    /**
    * 调用存储过程（有ID）
    * */
    public ResultSet selectStor(String name, String id) throws SQLException {
        return this.select("call "+name+"("+id+")");
    }
    public ResultSet selectStors(String name, String id) throws SQLException {
        String sql = "call "+name+"("+id+")";
        PreparedStatement ps = conn.prepareStatement(sql);
        return ps.executeQuery();
    }
    /**
     * 重写：调用存储过程（无ID）
     * */
    public ResultSet selectStor(String name) throws SQLException {
        return this.select("call "+name+"()");
    }

    /**
     * 原始插入功能
     * */
    public void insert(String sql) throws SQLException {
        stat.executeUpdate(sql);
    }
    /**
     * 重写：根据（表名，值）插入数据
     * */
    public void insert(String table, String val) throws SQLException {
        this.insert("insert into "+table+" value("+val+")");
    }

    /**
     * 原始删除功能
     * */
    public void delete(String t, String w) throws SQLException {
        stat.executeUpdate("delete from "+t+" where "+w);
    }
    /**
     * 调用存储过程 -> 删除
     * */
    public void delStored(String name, String id) throws SQLException {
        this.selectStor(name, id);
    }

    /**
     * 原始更新功能
     * */
    public void update(String table, String set, String w) throws SQLException{
        stat.executeUpdate("update "+table+" set "+set+" where "+w);
    }

    /**
     * 提取首条数据首个数据
     */
    public String resultFirst(ResultSet t) throws SQLException {
        return t.next() ? t.getString(1) : null;
    }

    /**
     * 整表查询（存储过程）
     * @return id列表
     */
    public List<String> getAllID(ResultSet id) throws SQLException {
        List<String> ids= new ArrayList<>();
        while (id.next())
            ids.add(
                    id.getString(1));
        id.close();
        return ids;
    }

    /**
     * 获取新增 ID
     * */
    public String findMaxId(String c, String t) throws SQLException {
        String sql = "select "+c+" from "+t+" order by "+c+" desc limit 0, 1";
        return this.resultFirst(this.select(sql));
    }

    /**
     * 关闭数据库
     * */
    public void close(ResultSet result) throws SQLException {
        util.close(conn, stat, result);
    }
    public void close() throws SQLException {
        util.close(conn, stat);
    }
    /**
     * 字符串拼接冒号
     * */
    public String str(String s){
        return '"'+s+'"';
    }

    /**
     * 查询电影列表
     * */
    public filmList getFilmList(ResultSet result) throws SQLException {
        // 创建对象
        filmList film = new filmList();
        while (result.next()) { // 遍历
            // 封装
            film.setName(
                    result.getString("fname"));
            film.setPrice(
                    result.getString("fprice"));
            film.setTime(
                    result.getString("ftime"));
            film.setId(
                    result.getString("fid"));
            film.setClassify(
                    result.getString("fclassify"));
        }
        return film;
    }

    public List<filmList> getFilmLists(ResultSet result) throws SQLException {
        List<filmList> filmLists = new ArrayList<>();
        while (result.next()){
            filmList film = new filmList();
            film.setName(
                    result.getString("filmname"));
            film.setId(
                    result.getString("filmid"));
            film.setTime(
                    result.getString("filmtime"));
            film.setPrice(
                    result.getString("filmprice"));
            film.setUrl(
                    "/dyp/filmServlet?id="+film.getId());
            filmLists.add(film);
        }
        result.close();
        return filmLists;
    }

    /**
     * 调用数据库存储过程
     * 查询订单
     * */
    public sel_orders getOrder(ResultSet result) throws SQLException {
        sel_orders o = new sel_orders();
        if (result.next()) {
            o.setId(result.getString("orderId"));
            o.setPrice(result.getString("orderPrice"));
            o.setTime(result.getString("orderTime"));
            o.setUser(result.getString("user"));
            o.setUserTel(result.getString("userTel"));
        }
        return o;
    }

    /**
     * 调用数据库存储过程
     * 查询电影票
     * */
    public ticket getTicket(ResultSet result) throws SQLException {
        ticket ticket = new ticket();   // 电影票明细类
        if (result.next()){
            // 封装
            ticket.setKid(result.getString("kid"));
            ticket.setInfo(result.getString("kinfo"));
            ticket.setTime(result.getString("ktime"));
            ticket.setFilmName(result.getString("fname"));
            ticket.setFilmTime(result.getString("ftime"));
            ticket.setPrice(result.getString("fprice"));
            ticket.setOid(result.getString("oid"));
        }
        return ticket;
    }

    /**
     * 数据库查询
     * 查询用户列表
     */
    public List<user> getUserList(ResultSet result) throws SQLException {
        List<user> userList = new ArrayList<>();
        while (result.next()){
            user user = new user();
            // 封装
            user.setId(result.getString("cid"));
            user.setName(result.getString("cname"));
            user.setTel(result.getString("ctel"));
            // 存入列表
            userList.add(user);
        }
        result.close();
        return userList;
    }

    /**
     * 数据库查询
     * 查询管理员列表
     */
    public List<admin> getAdminList(ResultSet result) throws SQLException {
        List<admin> adminLists = new ArrayList<>();
        while (result.next()){
            admin admin = new admin();
            // 封装
            admin.setId(result.getString("aid"));
            admin.setName(result.getString("aname"));
            // 存入列表
            adminLists.add(admin);
        }
        result.close();
        return adminLists;
    }

    public List<videos> getVideosList(ResultSet result) throws SQLException {
        List<videos> videosLists = new ArrayList<>();
        while (result.next()){
            videos videos = new videos();
            videos.setName(result.getString("fname"));
            videos.setId(
                    Integer.parseInt(
                            result.getString("fid")));
            videosLists.add(videos);
        }
        return videosLists;
    }

    public ticketNum getTicketNum(ResultSet result) throws SQLException {
        ticketNum ticketNum = new ticketNum();
        if (result.next()){
            ticketNum.setName(result.getString("nname"));
            ticketNum.setDay(
                    Integer.parseInt(
                            result.getString("dday")));
            ticketNum.setMoon(
                    Integer.parseInt(
                            result.getString("moon")));
            ticketNum.setAll(
                    Integer.parseInt(
                            result.getString("allNum")));
        }
        return ticketNum;
    }

    /**
     * 数据库查询
     * 统计（累计收益、今日收益、注册人数）
     */
    public sel_card getSelCard(ResultSet result) throws SQLException {
        sel_card selCard = new sel_card();
        if (result.next()){
            selCard.setToday(   // 封装类
                    Float.parseFloat(   // 格式转换
                            result.getString("tMoney")));   // 获取数据
            selCard.setAll(
                    Float.parseFloat(
                            result.getString("aMoney")));
            selCard.setPeople(
                    Integer.parseInt(
                            result.getString("people")));
        }
        return selCard;
    }
}

